using System;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using PDOGenerator.MapTypes;
using PDOGenerator.Util;

namespace PDOGenerator.DbToMoreGen
{
    public class FromDb
    {
        private Project _proj;
        private OleDbConnection _conn;


        public static GeneratorPM PM
        {
            get { return (PdoApp.Current as PdoApp).PM; }
        }

        public FromDb(Project p)
        {
            _proj = p;
        }


        private void SetExtendedTypeInfo(TableMap tbl)
        {
            TableMap baseTable = _proj.AllTables[tbl.BaseClassMoniker];

            if (null == baseTable || null == baseTable.ClassExtenderColumn)
            {
                string error = "Class extender column not defined in table " + tbl.BaseClassName;

                if (PM.IsBusy)
                {
                    PM.LastError = "Class deriviation problem.";
                    PM.LastErrorDetails += "\r\n" + error;
                    return;
                }
                else
                {
                    throw new InvalidOperationException(error);
                }
            }

            if (null == baseTable.ClassExtenderColumn.ForeignTable)
            {
                string error = "Derived class fkey relationship not properly defined for base table " + baseTable.TableName;

                if (PM.IsBusy)
                {
                    PM.LastError = "Class deriviation problem.";
                    PM.LastErrorDetails += "\r\n" + error;
                    return;
                }
                else
                {
                    throw new InvalidOperationException(error);
                }
            }

            string typeTableName = baseTable.ClassExtenderColumn.ForeignTable.TableName;
            if (string.IsNullOrEmpty(typeTableName))
            {
                string error = "Derived class extender column or fkey relationship not properly defined for base table " + baseTable.TableName;

                if (PM.IsBusy)
                {
                    PM.LastError = "Class deriviation problem.";
                    PM.LastErrorDetails += "\r\n" + error;
                    return;
                }
                else
                {
                    throw new InvalidOperationException(error);
                }
            }

            TableMap typeTable = baseTable.ClassExtenderColumn.ForeignTable;
            string typeTablePKeyName = typeTable.PrimaryKey().ColumnName;

            string typeExtensionColumnName = baseTable.ClassExtenderColumn.ColumnName;
            int pos = typeExtensionColumnName.IndexOf(Markers.PROXY_EXTENDER);
            typeExtensionColumnName = typeExtensionColumnName.Substring(pos + Markers.PROXY_EXTENDER.Length);

            //E.g. select DeviceSettingTypeGuid from Hydra.DeviceSettingType where ExtensionClassName = 'PPGDeviceSetting'
            string cmd = string.Format("select {0} from {1}.{2} where {3} = '{4}'",
                typeTablePKeyName, typeTable.SchemaName, typeTable.TableName, typeExtensionColumnName, tbl.ClassName);

            OleDbDataAdapter a = new OleDbDataAdapter(cmd, _conn);
            DataTable dt = new DataTable();
            a.Fill(dt);

            string extensionTypeGuid = null;

            if (dt.Rows.Count > 0)
            {
                extensionTypeGuid = dt.Rows[0][0].ToString();
            }

            if (null == extensionTypeGuid)
            {
                string error = "Extended type value not found in table " +
                    typeTable.SchemaName + "." + typeTable.TableName + " for derived class table " + tbl.ClassName;

                if (PM.IsBusy)
                {
                    PM.LastError = "Class deriviation problem.";
                    PM.LastErrorDetails += "\r\n" + error;
                    return;
                }
                else
                {
                    throw new InvalidOperationException(error);
                }
            }
            tbl.ExtensionTypeGuid = extensionTypeGuid;
            tbl.ExtensionTablePKName = typeTablePKeyName;

        }


        private void RemoveDeprecatedMapItems()
        {
            List<string> deadTableList = new List<string>();
            foreach (TableMap t in _proj.AllTables.Values)
            {
                if (t.IsPresentInDatabase == false)
                {
                    deadTableList.Add(t.TableMoniker);
                }

                List<string> deadColumnList = new List<string>();
                foreach (ColumnMap c in t.Columns.Values)
                {
                    if (c.IsPresentInDatabase == false)
                    {
                        deadColumnList.Add(c.ColumnName);
                    }
                }

                foreach (string cn in deadColumnList)
                {
                    t.Columns.Remove(cn);
                }
            }

            foreach (string tn in deadTableList)
            {
                _proj.AllTables.Remove(tn);
            }
        }


        /// <summary>
        /// Initialize existing map items prior to (re-)reading the database.
        /// </summary>
        private void InitializeMapItems()
        {

            foreach (TableMap t in _proj.AllTables.Values)
            {
                t.IsPresentInDatabase = false;

                foreach (ColumnMap c in t.Columns.Values)
                {
                    c.IsPresentInDatabase = false;
                    c.IsFK = false; // clear foreign key property value - reset later if applicablle
                }
            }
        }


        public void MergeProject()
        {
            try
            {
                _conn = new OleDbConnection(_proj.ConnectionString);
                _conn.Open();

                InitializeMapItems();

                UpdateTables();
                UpdateColumns();

                AddPrimaryKeys();
                AddForeignKeys();

                SetColumnIdentification();
                ResolveNameCollisions();

                // If UpdateClassExtensions fails, display exception message and give user
                // a chance to change project settings.
                UpdateClassExtensions();

                RemoveDeprecatedMapItems();

                Project.SyncSchemaProjectTables(_proj);

                _conn.Close();
            }
            catch (Exception ex)
            {
                if (PM.IsBusy)
                {
                    PM.LastError = "Database merge problem: " + ex.Message;
                    PM.LastErrorDetails = ex.ToString();
                }
                else
                {
                    MessageDialog.ShowMessage("Database merge problem: ", ex.Message, ex.ToString());
                }
            }

        }


        private void SetColumnIdentification()
        {

            foreach (TableMap t in _proj.AllTables.Values)
            {
                foreach (ColumnMap c in t.Columns.Values)
                {
                    string columnName = c.ColumnName;

                    c.SetObjectNames(t);

                    if (columnName.Contains(MapTypes.Markers.PROXY_PKEY))
                    {
                        t.BasePKeyColumnName = columnName;
                    }

                    if (columnName.Contains(MapTypes.Markers.PROXY_EXTENDER))
                    {
                        t.ClassExtenderColumnName = columnName;
                    }

                    if (columnName.Contains(MapTypes.Markers.LOOKUP_KEY_COLUMN))
                    {
                        t.LookupKeyColumnName = columnName;
                    }

                    // Enum colums are always readonly objects
                    if (c.IsFK && c.ForeignTable.IsLookup)
                    {
                        c.ForeignKeyType = ForeignKeyTypes.KeyAndReadonlyObject;
                    }

                }
            }
        }


        private void ResolveNameCollisions()
        {

            foreach (TableMap tbl in _proj.AllTables.Values)
            {

                // Ensure we have unique child object names (foreign key references)
                List<string> duplicateChildObjectNames = new List<string>();

                foreach (ColumnMap col1 in tbl.Columns.Values)
                {
                    foreach (ColumnMap col2 in tbl.Columns.Values)
                    {
                        if (col1 != col2 && !string.IsNullOrEmpty(col2.ObjectPropertyName) && col2.ObjectPropertyName == col1.ObjectPropertyName)
                        {
                            // found a name collision
                            duplicateChildObjectNames.Add(col1.ObjectPropertyName);
                        }
                    }
                }

                foreach (ColumnMap col in tbl.Columns.Values)
                {
                    if (col.IsFK && duplicateChildObjectNames.Contains(col.ObjectPropertyName))
                    {
                        col.ObjectPropertyName = col.ForeignType + "From" + col.SafeColumnName;
                    }
                }


                // Ensure we have unique reference list names (many side of one to many foreign key references)
                List<string> duplicateFKeyPrimaryTables = new List<string>();

                foreach (ColumnMap col1 in tbl.Columns.Values)
                {
                    foreach (ColumnMap col2 in tbl.Columns.Values)
                    {
                        if (col1 != col2 && col1.IsCollection && col2.IsCollection && col2.ReferencedListName == col1.ReferencedListName)
                        {
                            // found multiple reference to the same foreign table
                            duplicateFKeyPrimaryTables.Add(col1.ReferencedListName);
                        }
                    }
                }

                foreach (ColumnMap col in tbl.Columns.Values)
                {
                    if (col.IsCollection)
                    {
                        if (duplicateFKeyPrimaryTables.Contains(col.ReferencedListName))
                        {
                            col.ReferencedListName += "By" + _proj.AllTables[col.CollectionTableMoniker].Columns[col.CollectionColumn].SafeObjectName;
                        }
                        col.ObjectPropertyName = col.ReferencedListName + "List";
                    }
                }
            }
        }


        private void AddForeignKeys()
        {
            string SQLText = _proj.Database.Schema.FKSQL;

            OleDbDataAdapter a = new OleDbDataAdapter(SQLText, _conn);
            DataTable dt = new DataTable();
            a.Fill(dt);

            foreach (DataRow row in dt.Rows)
            {
                string fTableName = row["foreign_table"].ToString();
                string fSchemaName = row["fSchemaName"].ToString();
                string fTableMoniker = fSchemaName + "." + fTableName;
                string fColName = row["foreign_col1"].ToString();

                string pTableName = row["primary_table"].ToString();
                string pSchemaName = row["pSchemaName"].ToString();
                string pTableMoniker = pSchemaName + "." + pTableName;
                string pColName = row["primary_col1"].ToString();

                TableMap fTable = _proj.AllTables[fTableMoniker];
                ColumnMap fCol = fTable.Columns[fColName];

                TableMap pTable = _proj.AllTables[pTableMoniker];
                ColumnMap pCol = pTable.Columns[pColName];

                fCol.IsFK = true;
                fCol.ReferencedListName = fTable.ClassName;
                fCol.CollectionTableMoniker = pTable.TableMoniker;

                // Build unique identifying key-name
                string collectionColumnKey = SafeClassName(fTableName) + "_" + SafeClassName(fColName);

                ColumnMap pCollection;
                if (pTable.Columns.ContainsKey(collectionColumnKey))
                {
                    // Found collection already loaded from data file.
                    pCollection = pTable.Columns[collectionColumnKey];
                    pCollection.IsPresentInDatabase = true;
                }
                else
                {
                    // Add a collection to pTable
                    pCollection = new ColumnMap(pTable);
                    pCollection.ColumnName = collectionColumnKey;
                    pCollection.CollectionTableMoniker = fTable.TableMoniker;
                    pCollection.CollectionColumn = fCol.ColumnName;
                    pCollection.IsCollection = true;
                    pCollection.CollectionType = CollectionTypes.Bag;
                    pCollection.ReferencedListName = fTable.ClassName; // NOTE: May be revised when resolving duplicate names.
                    pCollection.IsPresentInDatabase = true;

                    pTable.Columns.Add(collectionColumnKey, pCollection);
                }
            }
        }

        private void AddPrimaryKeys()
        {
            string SQLText = _proj.Database.Schema.PKSQL;

            // first remove all primary keys
            foreach (TableMap t in _proj.AllTables.Values)
            {
                foreach (ColumnMap c in t.Columns.Values)
                {
                    c.IsPK = false;
                }
            }

            OleDbDataAdapter a = new OleDbDataAdapter(SQLText, _conn);
            DataTable dt = new DataTable();
            a.Fill(dt);

            foreach (DataRow row in dt.Rows)
            {
                string tableName = row["TableName"].ToString();
                string schemaName = row["SchemaName"].ToString();

                string tableMoniker = schemaName + "." + tableName;

                string columnName = row["ColName"].ToString();

                // find the table
                if (!_proj.AllTables.ContainsKey(tableMoniker))
                    continue; // for things like dbo.sysdiagrams

                TableMap t = _proj.AllTables[tableMoniker];

                if (!t.Columns.ContainsKey(columnName))
                    continue;// should probably throw exception heree

                ColumnMap c = t.Columns[columnName];

                c.IsPK = true;
            }
        }

        private void UpdateColumns()
        {
            string SQLText = _proj.Database.Schema.ColSQL;

            OleDbDataAdapter a = new OleDbDataAdapter(SQLText, _conn);
            DataTable dt = new DataTable();
            a.Fill(dt);

            foreach (DataRow row in dt.Rows)
            {
                string tableName = row["TableName"].ToString();
                string schemaName = row["SchemaName"].ToString();

                string tableMoniker = schemaName + "." + tableName;

                string columnName = row["ColumnName"].ToString();
                ColumnMap c;


                // find the table
                if (!_proj.AllTables.ContainsKey(tableMoniker))
                {
                    continue;
                }

                TableMap t = _proj.AllTables[tableMoniker];

                if (t.Columns.ContainsKey(columnName))
                {
                    c = t.Columns[columnName];
                    c.IsPresentInDatabase = true;
                }
                else
                {
                    c = new ColumnMap(t);
                    c.ColumnName = columnName;
                    c.IsPresentInDatabase = true;

                    t.Columns.Add(columnName, c);
                }

                // map column properties
                c.DBType = row["ColumnType"].ToString();

                TypeMap tm = _proj.Database.FindType(c.DBType);
                if (tm == null)
                {
                    throw new InvalidOperationException("Unknown ColumnType of " + c.DBType + " for column " + c.ColumnName + " in table " + t.TableName);
                }

                c.DataType = _proj.Database.FindType(c.DBType).csType[0];
                c.Nullable = (Convert.ToInt16(row["IsNullable"])) == 1;
                c.Identity = (Convert.ToInt16(row["IsIdentity"])) == 1;
                c.ColumnPrecision = ((DBNull.Value != row["prec"]) ? Convert.ToInt16(row["prec"]) : -999);
                c.DatabaseDefaultValue = row["DefaultValue"].ToString();
                c.IsSingularRelationship =
                    c.IsUnique = (row["IsUnique"] != DBNull.Value);
                c.Calculated = (Convert.ToInt16(row["IsComputed"])) == 1;

                if (t.IsView || c.Identity || c.Calculated)
                {
                    c.ReadOnly = true;
                }

            }
        }



        public static string SafeClassName(string name)
        {
            string safeName = name;

            if (safeName.Length > 3 && safeName.Substring(0, 3) == "tbl")
            {
                safeName = safeName.Substring(3);
            }

            // Remove derived class decoration 
            int pos = safeName.IndexOf(Markers.PROXY_DERIVED);
            if (pos > -1)
            {
                safeName = safeName.Substring(0, pos);
            }

            // Remove Lookup table decoration 
            pos = safeName.IndexOf(Markers.LOOKUP_TAG);
            if (pos > -1)
            {
                safeName = safeName.Substring(0, pos);
            }


            if (safeName.Contains(Markers.PROXY_PKEY))
            {
                safeName = safeName.Replace(Markers.PROXY_PKEY, "BaseKey");
            }

            pos = safeName.IndexOf(Markers.LOOKUP_KEY_COLUMN);
            if (pos > -1)
            {
                safeName = safeName.Substring(0, pos);
            }

            pos = safeName.IndexOf(Markers.PROXY_EXTENDER);
            if (pos > -1)
            {
                safeName = safeName.Substring(0, pos);
            }

            return safeName;
        }

        private string Moniker(string schema, string table)
        {
            string moniker;
            if (string.IsNullOrEmpty(schema))
            {
                moniker = _proj.DefaultSchemaName;
            }
            else
            {
                moniker = schema;
            }
            moniker += ".";

            int pos = table.IndexOf(Markers.PROXY_DERIVED);
            if (pos > -1)
            {
                moniker += table.Substring(pos + Markers.PROXY_DERIVED.Length);
            }
            return moniker;
        }

        private TableMap GetTableByClassName(string className)
        {
            foreach (TableMap t in _proj.AllTables.Values)
            {
                string fullName = t.SchemaName + "." + t.ClassName;
                if (fullName.Equals(className))
                {
                    return t;
                }
            }
            return null;
        }

        private void UpdateTables()
        {
            string SQLText = _proj.Database.Schema.TableSQL;

            OleDbDataAdapter a = new OleDbDataAdapter(SQLText, _conn);
            DataTable dt = new DataTable();
            try
            {
                a.Fill(dt);
            }
            catch (OleDbException ex)
            {
                throw new Exception("Check for duplicate table names in different schema namespaces.", ex);
            }

            foreach (DataRow row in dt.Rows)
            {
                string schemaName = row["SchemaName"].ToString();
                string tableName = row["name"].ToString();
                string tableMoniker = schemaName + "." + tableName;

                TableMap t;


                // Add table to master table collection on project...
                if (_proj.AllTables.ContainsKey(tableMoniker))
                {
                    t = _proj.AllTables[tableMoniker];
                    t.IsPresentInDatabase = true;
                }
                else
                {
                    t = new TableMap(_proj);
                    t.SchemaName = schemaName;
                    t.TableName = tableName;
                    t.IsPresentInDatabase = true;

                    _proj.AllTables.Add(tableMoniker, t);
                }

                t.IsUpdateable = (Convert.ToInt16(row["IsUpdateable"])) == 1;

                t.IsProxyDerived = tableName.Contains(Markers.PROXY_DERIVED);
                t.IsLookup = tableName.Contains(Markers.LOOKUP_TAG);
                t.ClassName = SafeClassName(tableName);

                // map table properties                
                if (Convert.ToInt16(row["IsView"]) == 0)
                {
                    t.IsView = false;
                    t.ReadOnly = false;
                }
                else
                {
                    t.IsView = true;
                    t.ReadOnly = true;
                }

                //try
                //{
                //    t.RowCount = Convert.ToInt32(row["rowcnt"]);
                //}
                //catch (InvalidCastException)    // eg overflow
                //{
                //    t.RowCount = 1000;  // anything more than 100 should be OK
                //}
            }

        }

        /// <summary>
        /// Set or update class inheriance info.
        /// </summary>
        private void UpdateClassExtensions()
        {
            foreach (TableMap t in _proj.AllTables.Values)
            {
                string tableName = t.TableName;

                if (t.IsProxyDerived && t.IsPresentInDatabase)
                {
                    int pos = tableName.IndexOf(Markers.PROXY_DERIVED);
                    t.BaseClassName = tableName.Substring(pos + Markers.PROXY_DERIVED.Length);
                    if (t.BaseClassName.Contains("."))
                    {
                        TableMap baseTable = GetTableByClassName(t.BaseClassName);
                        t.BaseClassMoniker = baseTable.TableMoniker;
                    }
                    else
                    {
                        string baseTableName = _proj.DefaultSchemaName + "." + t.BaseClassName;
                        TableMap baseTable = GetTableByClassName(baseTableName);
                        if (null == baseTable)
                        {
                            string error = "Unable to find base Table \"" + baseTableName + "\" for derived Table \"" +
                                t.TableName + "\".  Is your primary schema set correctly?";

                            if (PM.IsBusy)
                            {
                                PM.LastError = "Class deriviation problem.";
                                PM.LastErrorDetails += "\r\n" + error;
                                return;
                            }
                            else
                            {
                                throw new InvalidOperationException(error);
                            }
                        }
                        t.BaseClassMoniker = baseTable.TableMoniker;
                    }
                }
            }

            // Set extended type information in seperate pass to process dependencies in correct order.
            foreach (TableMap t in _proj.AllTables.Values)
            {
                if (t.IsProxyDerived && t.IsPresentInDatabase)
                {
                    SetExtendedTypeInfo(t);
                }
            }
        }


    }
}
